"""
对数据库中的图书信息进行增删改查
"""

from pymysql import Connection


# 构建 mysql 连接
def getConnection():
    conn = Connection(
        host='localhost',
        port=3306,
        user='root',
        password='root',
        autocommit=True
    )
    # 选择数据库
    conn.select_db('demo')
    return conn


# 查询
def query(isShow):
    # 获取数据库连接对象
    conn = getConnection()
    # 获取游标对象
    cursor = conn.cursor()
    # 执行sql
    cursor.execute('select * from book_info')
    books = cursor.fetchall()
    if isShow:
        print(f'*************** 查询图书 ***************')
    if len(books) == 0:
        print('目前还没有图书哦！')
    else:
        count = 1
        for book in books:
            print(f'序号：{count}\t书名：《{book[0]}》\t作者：{book[1]}\t价格：{book[2]}')
            count += 1
        print(f'共有{count - 1}条图书信息!')
        print()

    # 关闭连接
    conn.close()
    return books


# 添加图书
def add():
    print(f'*************** 添加图书 ***************')
    book = input('请输入要添加的图书名：\n')
    author = input('请输入该书的作者：\n')
    price = float(input('请输入该书的价格：\n'))

    # 获取数据库连接对象
    conn = getConnection()
    # 获取游标对象
    cursor = conn.cursor()
    # 执行 sql
    try:
        cursor.execute(f"insert into book_info (book,author,price) values('{book}','{author}',{price})")
        print(f"添加成功！（继续添加请输入1，查询图书请输入2，输入其它任意键返回主菜单）")
        ch = input()
        if ch == '1':
            add()
        elif ch == '2':
            query(False)
    except Exception as e:
        print(f"添加失败，失败信息为：{e}，请重新添加！")
        add()
    finally:
        # 关闭连接
        conn.close()


# 删除图书
def delete(isAgain):
    if isAgain:
        print(f'*************** 删除图书 ***************')
    books = query(False)
    if isAgain:
        count = input("请输入您要删除的图书序号：\n")
    else:
        count = input("输入错误，请重新输入您要删除的图书序号：\n")
    try:
        count = int(count)
        book = books[count - 1]
        # 获取数据库连接对象
        conn = getConnection()
        # 获取游标对象
        cursor = conn.cursor()
        # 执行 sql
        try:
            cursor.execute(f"delete from book_info where "
                           f"book = '{book[0]}' and author = '{book[1]}' and price = {book[2]}")
            print(f"删除成功！（继续删除请输入1，查询图书请输入2，输入其它任意键返回主菜单）")
            ch = input()
            if ch == '1':
                delete(True)
            elif ch == '2':
                query(False)
        except Exception as e:
            print(f"删除失败，失败信息为：{e}，请重试！")
            delete(True)
        finally:
            # 关闭连接
            conn.close()
    except ValueError | IndexError:
        delete(False)


if __name__ == '__main__':
    add()
    delete(True)
    query(True)
